<?php
// load library
require 'class/php-excel.class.php';
require_once '../include/common.inc.php';
if (!checkAdmin()) {
    header("LOCATION:index.php");
    exit;
}
getMyParam('typeselect');
getMyParam('select2');


// type 1
if ($typeselect == 1){
 $data =array();
 $column=array();
 if(isset($excel_sql_ary))
  {
   foreach($excel_sql_ary as $k=>$v)
	  {
	    $column[]=$k;	  
	  }  
  }
// first column
$data[]=$column;


// Category 
// file name
   $sql = "SELECT `id`,`fid`,`name`,`namezh` FROM `category` WHERE `id` = '$select2'";
    $tmp_ary = $db->get_one($sql);	
	$file_name = str_replace(" ","_",preg_replace("/<[^>]+>/","",$tmp_ary['name']));
	
	$category_name=array();
	$category_name['Category_name']=$tmp_ary['name'];
	$category_name['Category_namezh']=$tmp_ary['namezh'];
	
    //Finishes
	$finish_id_name = array();	
	$finish_idzh_name = array();
	$sql = "SELECT `id`, `name`,`namezh` FROM `p_finish_set`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$finish_id_name[$rs["id"]] = $rs['name'];
		$finish_idzh_name[$rs["id"]] = $rs['namezh'];
	}	
	//Setting
	$setting_id_name = array();
	$setting_idzh_name = array();
	$sql = "SELECT `id`, `name`,`namezh` FROM `p_setting`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$setting_id_name[$rs['id']] = $rs["name"];
		$setting_idzh_name[$rs['id']]= $rs["namezh"];
	}	
	
	$setting_pro_tab=array();
	$sql = "SELECT `setting_id`,`product_id`  FROM `product_p_setting` ";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$setting_pro_tab[$rs['product_id']][] = $rs["setting_id"];
		
	}	
	
	//Style
	$style_id_name = array();
	$style_idzh_name = array();
	$sql = "SELECT `id`, `name`,`namezh` FROM `p_style`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$style_id_name[$rs['id']] = $rs["name"];
		$style_idzh_name[$rs['id']] = $rs["namezh"];
		
	}	
	
	//Collection
	$collection_idzh_name = array();
	$sql = "SELECT `id`, `name`,`namezh` FROM `p_collection`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$collection_id_name[$rs['id']] = $rs["name"];
		$collection_idzh_name[$rs['id']] = $rs["namezh"];
	}

// sub category  id string
$sub_cat_str = GetCategoryTreeIDS($_categories,$select2,false);

//echo $sub_cat_str."<br/>";
// sub category  array 
$product_category_arr=array();
/*$sql = "SELECT `product_id`,`category_id` FROM `product_category` WHERE `category_id` IN (". $sub_cat_str .") ORDER BY product_id";
$query = $db->query($sql);
while($rs = $db->fetch_array($query)) 
{		    
	$product_category_arr[$rs["product_id"]][]= $rs['category_id'];	
	//echo $rs["product_id"]." _ ".$rs['category_id']."<br/>"; 			
}*/	


//sub Sub Category 
$subcategory_name=array();
$subcategory_namezh=array();
$sql = "SELECT `id`,`name`,`namezh` FROM `category` ";
$query = $db->query($sql);
while($rs = $db->fetch_array($query)) {
	$subcategory_name[$rs["id"]] = $rs['name'];
	$subcategory_namezh[$rs["id"]] = $rs['namezh'];				
}	


// fabric 
$fabric_id_name=array();
$sql = "SELECT `id`, `name`,`namezh` FROM `p_fabric_category` ";
$query = $db->query($sql);
while($rs = $db->fetch_array($query))
 {
	$fabric_id_name[$rs['id']] = strtoupper($rs['name']);
	//$fabric_arr[$rs['id']]['namezh'] = $rs['namezh'];
	
}

// leather 
$leather_id_name=array();
$sql = "SELECT `id`, `name`,`namezh` FROM `p_leather_category` ";
$query = $db->query($sql);
while($rs = $db->fetch_array($query)) 
{
	$leather_id_name[$rs['id']] = strtoupper($rs['name']);
	//$leather_id_name[$rs['id']] = $rs['namezh'];
	
}


$get_cat_ary = array();
$have_product_id_ary = array();	

$product_cat_ary = array();
$sql = "SELECT pc.`product_id` as product_id, pc.`category_id` as category_id,c.`name` as name,pc.`seq` as seq FROM `product_category` pc INNER JOIN `category` c ON pc.`category_id` = c.id WHERE pc.`category_id` IN (" . $sub_cat_str . ") ORDER BY pc.product_id";
$query = $db->query($sql);
    while($rs = $db->fetch_array($query)){	
	    if($rs['category_id']!=$select2)
	    $product_category_arr[$rs["product_id"]][]= $rs['category_id'];	
        //$get_cat_ary[$rs['product_id']][$rs['category_id']] = $rs['seq'];
        $have_product_id_ary[] = $rs['product_id'];
		//$product_cat_ary[$rs['product_id']][] = array("id"=>$rs['category_id'],"category" =>$rs['name'],"seq" =>$rs['seq']);
    }
	
$have_product_id_str = implode(",", $have_product_id_ary);
$sql = "SELECT p.id as product_id,p.*,pf.id as finish_id,ps.id as setting_id,psl.id as style_id,pcl.id as collection_id FROM `product` p LEFT OUTER JOIN `p_finish_set` pf ON p.finish_id = pf.id"
		." LEFT OUTER JOIN `p_setting` as ps ON p.setting_id = ps.id"
		." LEFT OUTER JOIN `p_style` as psl ON p.style_id = psl.id"
		." LEFT OUTER JOIN `p_collection` as pcl ON p.collection_id = pcl.id WHERE p.`id` IN (" . $have_product_id_str . ")    ORDER BY p.`code` ASC";

		$query = $db->query($sql);
		$colums2=array();	
//get product
		while($rs = $db->fetch_array($query)){
		$columns=array();
		$columns[]=$rs["code"];
		$columns[]=$rs["pcode"];
		$columns[]=$rs["name"];

		//web show
		if($rs['hidden_flag']==1)
		{$columns[]='N';
				}else
				{$columns[]='Y';}	
		// is news 
		if($rs['new_flag']==1){
			$columns[]='Y';
		}else
			{$columns[]='N';}	
		
		$columns[]=htmlentities($rs['comment']);
		$columns[]=$rs['pic'];
		$columns[]=$rs['small_pic'];
		//price
		//$columns[]=number_format($rs['price_2'],2);
		
		$columns[]=number_format($rs['price_2'],2);			
		$columns[]=number_format($rs['price_1'],2);		
		$columns[]=number_format($rs['price_3'],2);		
		$columns[]=number_format($rs['length'],2);
		$columns[]=number_format($rs['width'],2);
		$columns[]=number_format($rs['height'],2);
		$columns[]=number_format($rs['cbm'],3);
		$columns[]=number_format($rs['width2'],2);
		$columns[]=number_format($rs['length2'],2);
		$columns[]=number_format($rs['height2'],2);
		$columns[]=number_format($rs['cuft'],3);
		$columns[]=$rs['finishshown'];
		
		//fabric
		$columns[]=$rs['fabricshown'];
		//$columns[]=$fabric_arr[$rs['fabric_id']]['name'];
		
		
		//leather
		$columns[]=$rs['leathershown'];
		//$columns[]=$leather_arr[$rs['leather_id']]['name'];
	
		
		
		
		
		$columns[]=$rs['marbleshown'];
		//finish 		
		$columns[]=$finish_id_name[$rs['finish_id']];
		$columns[]=$fabric_id_name[$rs['fabric_cat']];
		$columns[]=$leather_id_name[$rs['leather_cat']];
		
		//setting
		//$columns[]=$setting_id_name[$rs['setting_id']];
		$cut=count($setting_pro_tab[$rs['product_id']]);
		if(isset($setting_pro_tab[$rs['product_id']])&&$cut>0)
		{
		  $str="";
		  $splt=" | ";		  
		  foreach($setting_pro_tab[$rs['product_id']] as $kk=>$vv)
		  {		
		    
			  if($kk>$cut-2)
			  {
			    $splt="";	
			  }
			   
              $str.=$setting_id_name[$vv].$splt;	
			 		  
		  }
		   $columns[]=$str;
		}else{		
		  $columns[]="";
		}
		
		
		$columns[]=$style_id_name[$rs['style_id']];
		$columns[]=$collection_id_name[$rs['collection_id']];
		//$columns[]=htmlentities($rs['info']);	
		$columns[]= html_entity_decode($rs["info"],ENT_QUOTES,"UTF-8");
		
		
		//Sub Category
		//$subs=array();
		$product_category_arr[$rs['product_id']]=array_unique($product_category_arr[$rs['product_id']]);		
		
		if(count($product_category_arr[$rs['product_id']])>0)
		{
						 
		  foreach($product_category_arr[$rs['product_id']] as $kk=>$vs)
		  {   
		   if(trim($vs)!="")			  
			{
			  if($kk==0)
			  {
			    $columns[]=$subcategory_name[$vs];					
			  }else  if($kk > 0)
			  {		 
				  for($i=0;$i<30;$i++)
				  {
					$colums2[$kk][]="";
				  }
				  $colums2[$kk][]=$subcategory_name[$vs];
				  for($i=0;$i<2;$i++)
				  {
				     $colums2[$kk][]="";
				  }
				  $colums2[$kk][]=$subcategory_namezh[$vs];
				  for($i=0;$i<5;$i++)
				  {
				   $colums2[$kk][]="";
				  } 		
               
			  }			  		  			  
			}
		  }
		  //$colums[]="";
		}
		else //空值
		{
		   $columns[]="";
	    }
		
		$columns[]=$rs['namezh'];
		$columns[]=$rs['commentzh'];		
		$columns[]=$subcategory_namezh[$product_category_arr[$rs['product_id']][0]];;		
		//$columns[]=$setting_idzh_name[$rs['setting_id']];		
			if(isset($setting_pro_tab[$rs['product_id']])&&$cut>0)
		{
		  $str="";
		  $splt=" | ";		  
		  foreach($setting_pro_tab[$rs['product_id']] as $kk=>$vv)
		  {		
		    
			  if($kk>$cut-2)
			  {
			    $splt="";	
			  }
			   
              $str.=$setting_idzh_name[$vv].$splt;	
			 		  
		  }
		   $columns[]=$str;
		}else{		
		  $columns[]="";
		}
		
		$columns[]=$style_idzh_name[$rs['style_id']];
		
		
		//$columns[]=$rs['leathershownzh'];
		//$columns[]=$rs['marbleshownzh'];
		//fabric	
		$columns[]=$fabric_arr[$rs['fabric_id']]['namezh'];
		
		//leather
		$columns[]=$leather_arr[$rs['leather_id']]['namezh'];		
		$columns[]=$rs['marbleshownzh'];
		//$columns[]=$rs['infozh'];
		$columns[]= html_entity_decode($rs["infozh"],ENT_QUOTES,"UTF-8");
		
		$data[]=$columns;
		if(isset($colums2)&&trim($colums2)!="")
		{
		  foreach($colums2 as $k22=>$v22)
		  {		
		     $data[]=$v22;
		  }
		  $colums2="";
	   }
}




 //generate file (constructor parameters are optional)
$xls = new Excel_XML('UTF-8', false, 'Sheet1');
$xls->addArray($data);
$xls->generateXML($file_name);
// type end
}else if ($typeselect == 2) {
    $sql = "SELECT `name`, `type` FROM `inventory` WHERE `id` = '$select2'";
    $tmp_ary = $db->get_one($sql);
	$file_name =  "Inventory_" .str_replace(" ","_",$tmp_ary['name']). ".xls";
    $inventory_type = $tmp_ary['type'];
    $inventory_name = $tmp_ary['name'];
		
	header("Content-Type: application/vnd.ms-excel; charset=UTF-8");	
	header("Accept-Ranges:bytes");
	header("Content-Disposition:attachment;filename=".preg_replace("/<[^>]+>/","",$file_name));

    if ($inventory_type == 1) {
        echo "Item#\tQty\tSpecial Price\tSpecial Price2\tLocation";
    } else {
        echo "Item#\tQty\tSpecial Price\tLocation";
    }

    $sql = "SELECT `product_id`, `inventory_id`, `qty`, `special_price`, `special_price2` FROM `product_inventory` WHERE `inventory_id`='$select2'";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $inventory_info_ary[$rs['product_id']] = $rs;
        $product_id_ary[] = $rs['product_id'];
    }

    $product_id_ary = array_unique($product_id_ary);
    $product_id_str = implode(",", $product_id_ary);
    $sql = "SELECT * FROM `product` WHERE `id` IN (" . $product_id_str . ") ORDER BY `code` DESC";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        echo "\n";
        echo $rs['code'] . "\t";
        echo $inventory_info_ary[$rs['id']]['qty'] . "\t";
        echo $inventory_info_ary[$rs['id']]['special_price'] . "\t";
        if ($inventory_type == 1) {
            echo $inventory_info_ary[$rs['id']]['special_price2'] . "\t";
        }
        echo $inventory_name . "\t";
    }

    exit;

}

?>